package com.sunda.spmswms.mapper;

import com.sunda.spmswms.entity.SapPackageListDtl;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * SAP装箱单明细表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-04-22
 */
@Mapper
public interface SapPackageListDtlMapper extends BaseMapper<SapPackageListDtl> {

    @Select("SELECT ROWNUM AS \"rowId\",\n " +
            "       PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "       PLD.ITEM AS \"item\",\n" +
            "       PLD.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "       PLD.LOADING_WERKS AS \"loadingWerks\",\n" +
            "       PLD.LOADING_WHS_LOCATION_CODE AS \"loadingWhsLocationCode\",\n" +
            "       PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "       PLD.GOODS_TYPE AS \"goodsType\",\n" +
            "       PB.BOX_NOTE AS \"boxNote\",\n" +
            "       PLD.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       PLD.LEAD_SEAL AS \"leadSeal\",\n" +
            "       PLD.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       PLD.SHIPMENT_CATEGORY AS \"shipmentCategory\",\n" +
            "       PLD.PLANNED_PACKING_QTY AS \"plannedPackingQty\",\n" +
            "       PLD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       PLD.PLANNED_PACKING_NUMBER AS \"plannedPackingNumber\",\n" +
            "       PLD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       SAP.UNIT_SALES AS \"unitSales\",\n" +
            "       SAP.NET_WEIGHT AS \"netWeight\",\n" +
            "       SAP.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "       SAP.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "       SAP.VOLUME_QTY AS \"volumeQty\",\n" +
            "       SAP.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "       SAP.LIGHT_HEAVY_MARK AS \"lightHeavyMark\"\n" +
            "FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    LEFT JOIN SAP_PACKAGE_BOX PB ON PLD.PACKAGE_LIST = PB.PACKAGE_LIST AND PLD.ITEM = PB.ITEM\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON PLD.MATERIAL_NO = SAP.MATERIAL_NO " +
            "WHERE PLD.PACKAGE_LIST = #{packageList} ORDER BY ROWNUM ASC ")
    List<Map<String, Object>> getSapPackageList(String packageList);

    @Select("SELECT DISTINCT PLD.WHS_OUT_UUID AS \"whsOutUuid\",\n" +
            "                SPB.BOX_NOTE AS \"boxNote\"\n" +
            "FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    LEFT JOIN SAP_PACKAGE_BOX SPB\n" +
            "        ON PLD.PACKAGE_LIST = SPB.PACKAGE_LIST AND PLD.ITEM = SPB.ITEM\n" +
            "WHERE PLD.GOODS_TYPE = #{goodsType} AND PLD.WHS_OUT_UUID = #{uuid}\n")
    List<Map<String, Object>> getWhsOutDtlX(@Param("uuid") String uuid, @Param("goodsType") String goodsType);

    @Update("UPDATE SAP_PACKAGE_LIST_DTL SET SPMS_STATUS = #{targetStatus} WHERE WHS_OUT_UUID = #{whsOutUuid} ")
    int uSapPackageListDtl(@Param("targetStatus") String targetStatus, @Param("whsOutUuid") String whsOutUuid);

    @Select("SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "                PLD.ITEM AS \"item\",\n" +
            "                PLD.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "                PLD.LOADING_WERKS AS \"loadingWerks\",\n" +
            "                PLD.LOADING_WHS_LOCATION_CODE AS \"loadingWhsLocationCode\",\n" +
            "                PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "                PLD.GOODS_TYPE AS \"goodsType\",\n" +
            "                PLD.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "                PLD.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "                PLD.LEAD_SEAL AS \"leadSeal\",\n" +
            "                PLD.MATERIAL_NO AS \"materialNo\",\n" +
            "                SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "                PLD.BASIC_UNIT AS \"basicUnit\",\n" +
            "                PLD.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "                PLD.PACKING_UNIT AS \"packingUnit\",\n" +
            "                PLD.CONTAINER_BRAND AS \"contaningBrand\",\n" +
            "                PLD.CUSTOMS_DECLARATION_NUMBER AS \"customsDeclarationNumber\",\n" +
            "                (CASE WHEN SAP.NUMERATOR IS NULL THEN 1 ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "                (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1 ELSE SAP.DENOMINATOR END) AS \"denominator\",\n" +
            "                SAP.NET_WEIGHT AS \"netWeight\",\n" +
            "                SAP.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "                SAP.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "                SAP.VOLUME_QTY AS \"volumeQty\",\n" +
            "                SAP.VOLUME_UNIT AS \"volumeUnit\"\n" +
            "         FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "                  LEFT JOIN MATERIAL_SAP SAP ON PLD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "         WHERE PLD.PACKAGE_LIST = #{packageList} AND PLD.GOODS_TYPE = 'M' ORDER BY ITEM ASC\n" +
            "         ) RE ORDER BY \"rowId\" ")
    List<Map<String, Object>> getSapPackageListDtlM(@Param("packageList") String packageList);
}
